from django.db import connection

from app.model.cs_stock_goods import CsStockGoods
from app.service import goods, stock
from app.service.goods import query_goods_img_by_ids
from libs import common
from libs.db import escape_fields


def get_goods_stock_with_status(shop_id, cashier_id, goods_id, status=0):
    try:
        cs_stock_goods = CsStockGoods.objects.filter(shop_id=shop_id, cashier_id=cashier_id,
                                                     goods_id=goods_id, status=status).values().get()
    except CsStockGoods.DoesNotExist:
        cs_stock_goods = {}
    return cs_stock_goods


# 获取库存列表
def get_stock_goods_list(status, page, where_str='', bind_arr=[]):
    arr = []
    sg_fields = ['id', 'order_id', 'shop_id', 'cashier_id',
                 'goods_id', 'cost_price', 'amount', 'subtotal',
                 'status', 'created', 'updated']
    sg_escaped_fields = escape_fields(sg_fields, 'sg')
    tg_fields = ['class_id', 'goods_name', 'scount', 'is_ban',
                 'create_time', 'state', 'discount_ratio',
                 'is_community', 'is_warehouse', 'point_ratio', 'barcode',
                 'unit', 'guardian_ratio', 'spec_typ', 'scale_no',
                 'product_date', 'guarantee_period', 'guarantee_type']

    tg_escaped_fields = escape_fields(tg_fields, 'tg')
    merge_fields = sg_fields + tg_fields
    merge_escaped_fields = sg_escaped_fields + tg_escaped_fields
    merge_escaped_field_str = ','.join(merge_escaped_fields)
    sql = 'select {} from cs_stock_goods sg left join table_goods tg on tg.id=sg.goods_id' \
          ' where sg.`status`=%s'.format(merge_escaped_field_str)
    bindings = [status]
    if where_str != '':
        sql += where_str
        bindings += bind_arr
    sql += ' order by sg.id desc'

    limit = page.get_limit()
    sql += limit
    goods_ids = []
    with connection.cursor() as cursor:
        cursor.execute(sql, bindings)
        rows = cursor.fetchall()
        for row in rows:
            data = {}
            for i, field in enumerate(merge_fields):
                val = row[i]
                data[field] = val
                if field == 'goods_id':
                    goods_ids.append(str(val))
            arr.append(data)
        if len(goods_ids) > 0:
            goods_ids_joined = ','.join(goods_ids)
            goods_img_map = query_goods_img_by_ids(goods_ids_joined, cursor)
            for item in arr:
                item_goods_id = item.get('goods_id', 0)
                if item_goods_id > 0:
                    item['goods_img'] = goods_img_map.get(item_goods_id, '')
                else:
                    item['goods_img'] = ''
    return arr


# 获取库存列表
def count_stock_goods(status, where_str='', bind_arr=None):
    if bind_arr is None:
        bind_arr = []
    num = 0
    sql = 'select count(*) num from cs_stock_goods sg left join table_goods tg on tg.id=sg.goods_id' \
          ' where sg.`status`=%s'
    bindings = [status]
    if where_str != '':
        sql += where_str
        bindings += bind_arr
    with connection.cursor() as cursor:
        cursor.execute(sql, bindings)
        row = cursor.fetchone()
        if row is not None:
            num = row[0]
    return num


def update_goods_stock(shop_id, cashier_id, post_data):
    res = {
        'stock_id': 0,
        'opt_typ': '',
        'amount': 0,
        'subtotal': 0,
        'cost_price': 0,
    }
    row_id = 0
    goods_id = common.to_int(post_data.get('goods_id', 0))
    input_amount = common.to_int(post_data.get('amount', 0))
    input_amount_typ = type(input_amount)
    if input_amount_typ is int:
        if input_amount == -1:
            input_amount = None
        elif input_amount < 0:
            return res, row_id, '添加库存数量错误'
    if goods_id == 0:
        return res, row_id, '无效的商品ID'
    try:
        if input_amount is None or input_amount > 0:
            db_goods_data = goods.query_goods_by_id(shop_id, goods_id)
            db_goods_id = db_goods_data.get('id', 0)
            if db_goods_id == 0:
                return res, row_id, '商品不存在'
            res, err = stock.save_goods_to_stock(shop_id, cashier_id, goods_id, db_goods_data, input_amount)
            if err is not None:
                return res, row_id, err
        else:
            err = stock.del_goods_from_stock(shop_id, cashier_id, goods_id)
            if err is not None:
                return res, row_id, err
            res['opt_typ'] = 'del'
    except Exception as e:
        err = '更新库存异常'
        print(err, e.args)
    return res, row_id, err
